﻿using System;
using System.Collections.Generic;
using PtsNames = Citi.PTSConsolidation.Domain.EntrepotDatabaseNames.ApplicationBookOfWork;

namespace Citi.PTSConsolidation.Domain
{
    public class ApplicationBookOfWork
    {
        #region Mapping

        public static readonly Dictionary<string, string> ColumnMappings = new Dictionary<string, string>
        {
            {PtsNames.ApplicationId, "ApplicationId"},
            //{PtsNames.ApplicationName, "ApplicationName"},
            //{PtsNames.ApplicationRetirementDate, "ApplicationRetirementDate"},
            {PtsNames.AccountLevel1, "AccountLevel1"},
            {PtsNames.AccountLevel2, "AccountLevel2"},
            {PtsNames.AccountLevel3, "AccountLevel3"},
            {PtsNames.InitiativeId, "InitiativeId"},
            {PtsNames.InitiativeName, "InitiativeName"},
            {PtsNames.ProgramId, "ProgramId"},
            {PtsNames.ProgramName, "ProgramName"},
            {PtsNames.ProjectId, "ProjectId"},
            {PtsNames.ProjectName, "ProjectName"},
            {PtsNames.ProgramManager, "ProgramManager"},
            {PtsNames.ProgramStartDate, "ProgramStartDate"},
            {PtsNames.ProgramEndDate, "ProgramEndDate"},
            {PtsNames.ProjectStatus, "ProjectStatus"},
            {PtsNames.ProjectStartDate, "ProjectStartDate"},
            {PtsNames.ProjectEndDate, "ProjectEndDate"},
            {PtsNames.ProjectManager, "ProjectManager"},
            {PtsNames.BillingClientGocCode, "BillingClientGocCode"},
            {PtsNames.Client3Name, "Client3Name"},
            {PtsNames.Client4Name, "Client4Name"},
            {PtsNames.Client5Name, "Client5Name"},
            {PtsNames.Client6Name, "Client6Name"},
            {PtsNames.Client7Name, "Client7Name"},
            {PtsNames.Client8Name, "Client8Name"},
            {PtsNames.Client9Name, "Client9Name"},
            {PtsNames.Client10Name, "Client10Name"},
            {PtsNames.ClientGocCode, "ClientGocCode"},
            {PtsNames.ClientRegion, "ClientRegion"},
            {PtsNames.System5Name, "System5Name"},
            {PtsNames.System6Name, "System6Name"},
            {PtsNames.System7Name, "System7Name"},
            {PtsNames.System8Name, "System8Name"},
            {PtsNames.System9Name, "System9Name"},
            {PtsNames.System10Name, "System10Name"},
            {PtsNames.SystemGocCode, "SystemGOCCode"},
            {PtsNames.SystemRegion, "SystemRegion"},
            {PtsNames.Lob, "LOB"},
            {PtsNames.RuntheBankBuildTheBank, "RuntheBankBuildtheBank"},
            {PtsNames.ProgramProjectLinkIndicator, "ProgramProjectLinkIndicator"},
            {PtsNames.WorkTypeFinancials, "WorkTypeFinancials"},
            {PtsNames.InitiativeInvestCategory, "InitiativeInvestimentCategory"},
            {PtsNames.ProgramInvestimentCategory, "ProgramInvestimentCategory"},
            {PtsNames.ProgramCapitalizedIndicator, "ProgramCapitalizedIndicator"},
            {PtsNames.ProgramStatus, "ProgramStatus"},
            {PtsNames.ProjectCapitalization, "ProjectCapitalization"},
            {PtsNames.BowFlag, "BowFlag"},
            {PtsNames.BowFlag2, "BowFlag2"},
            {PtsNames.ProjectType, "ProjectType"},
            {PtsNames.ProgramCarryoverIndicator, "ProgramCarryoverIndicator"},
            {PtsNames.TaxonomyName, "TaxonomyName"},
            {PtsNames.CuRate, "CURate"},
            {PtsNames.FirstOfProgramDescr, "FirstOfProgramDesc"},
            {PtsNames.LobParent, "LobParent"},
            {PtsNames.AdjustedFlag, "AdjustedFlag"},
            {PtsNames.January, "January"},
            {PtsNames.February, "February"},
            {PtsNames.March, "March"},
            {PtsNames.April, "April"},
            {PtsNames.May, "May"},
            {PtsNames.June, "June"},
            {PtsNames.July, "July"},
            {PtsNames.August, "August"},
            {PtsNames.September, "September"},
            {PtsNames.October, "October"},
            {PtsNames.November, "November"},
            {PtsNames.December, "December"},
            {PtsNames.YearToDate, "YearToDate"},
            {PtsNames.FullYear, "FullYear"},
            {"ReferenceDate", "ReferenceDate"},
            {"LastUpdate", "LastUpdate"}
        };

        #endregion

        #region Query Strings

        public static string GetEntrepotQueryString(int year, long projectIdStart, long projectIdEnd)
        {
            #region Columns
            var query = string.Concat(PtsNames.ApplicationId, ", ");
            //query = string.Concat(query, PtsNames.ApplicationName, ", ");
            //query = string.Concat(query, PtsNames.ApplicationRetirementDate, ", ");
            query = string.Concat(query, PtsNames.AccountLevel1, ", ");
            query = string.Concat(query, PtsNames.AccountLevel2, ", ");
            query = string.Concat(query, PtsNames.AccountLevel3, ", ");
            query = string.Concat(query, PtsNames.InitiativeId, ", ");
            query = string.Concat(query, PtsNames.InitiativeName, ", ");
            query = string.Concat(query, PtsNames.ProgramId, ", ");
            query = string.Concat(query, PtsNames.ProgramName, ", ");
            query = string.Concat(query, PtsNames.ProjectId, ", ");
            query = string.Concat(query, PtsNames.ProjectName, ", ");
            query = string.Concat(query, PtsNames.ProgramManager, ", ");
            query = string.Concat(query, PtsNames.ProgramStartDate, ", ");
            query = string.Concat(query, PtsNames.ProgramEndDate, ", ");
            query = string.Concat(query, PtsNames.ProjectStatus, ", ");
            query = string.Concat(query, PtsNames.ProjectStartDate, ", ");
            query = string.Concat(query, PtsNames.ProjectEndDate, ", ");
            query = string.Concat(query, PtsNames.ProjectManager, ", ");
            query = string.Concat(query, PtsNames.BillingClientGocCode, ", ");
            query = string.Concat(query, PtsNames.Client3Name, ", ");
            query = string.Concat(query, PtsNames.Client4Name, ", ");
            query = string.Concat(query, PtsNames.Client5Name, ", ");
            query = string.Concat(query, PtsNames.Client6Name, ", ");
            query = string.Concat(query, PtsNames.Client7Name, ", ");
            query = string.Concat(query, PtsNames.Client8Name, ", ");
            query = string.Concat(query, PtsNames.Client9Name, ", ");
            query = string.Concat(query, PtsNames.Client10Name, ", ");
            query = string.Concat(query, PtsNames.ClientGocCode, ", ");
            query = string.Concat(query, PtsNames.ClientRegion, ", ");
            query = string.Concat(query, PtsNames.System5Name, ", ");
            query = string.Concat(query, PtsNames.System6Name, ", ");
            query = string.Concat(query, PtsNames.System7Name, ", ");
            query = string.Concat(query, PtsNames.System8Name, ", ");
            query = string.Concat(query, PtsNames.System9Name, ", ");
            query = string.Concat(query, PtsNames.System10Name, ", ");
            query = string.Concat(query, PtsNames.SystemGocCode, ", ");
            query = string.Concat(query, PtsNames.SystemRegion, ", ");
            query = string.Concat(query, PtsNames.Lob, ", ");
            query = string.Concat(query, PtsNames.RuntheBankBuildTheBank, ", ");
            query = string.Concat(query, PtsNames.ProgramProjectLinkIndicator, ", ");
            query = string.Concat(query, PtsNames.WorkTypeFinancials, ", ");
            query = string.Concat(query, PtsNames.InitiativeInvestCategory, ", ");
            query = string.Concat(query, PtsNames.ProgramInvestimentCategory, ", ");
            query = string.Concat(query, PtsNames.ProgramCapitalizedIndicator, ", ");
            query = string.Concat(query, PtsNames.ProgramStatus, ", ");
            query = string.Concat(query, PtsNames.ProjectCapitalization, ", ");
            query = string.Concat(query, PtsNames.BowFlag, ", ");
            query = string.Concat(query, PtsNames.BowFlag2, ", ");
            query = string.Concat(query, PtsNames.ProjectType, ", ");
            query = string.Concat(query, PtsNames.ProgramCarryoverIndicator, ", ");
            query = string.Concat(query, PtsNames.TaxonomyName, ", ");
            query = string.Concat(query, PtsNames.CuRate, ", ");
            query = string.Concat(query, PtsNames.FirstOfProgramDescr, ", ");
            query = string.Concat(query, PtsNames.LobParent, ", ");
            query = string.Concat(query, PtsNames.AdjustedFlag, ", ");
            query = string.Concat(query, PtsNames.January, ", ");
            query = string.Concat(query, PtsNames.February, ", ");
            query = string.Concat(query, PtsNames.March, ", ");
            query = string.Concat(query, PtsNames.April, ", ");
            query = string.Concat(query, PtsNames.May, ", ");
            query = string.Concat(query, PtsNames.June, ", ");
            query = string.Concat(query, PtsNames.July, ", ");
            query = string.Concat(query, PtsNames.August, ", ");
            query = string.Concat(query, PtsNames.September, ", ");
            query = string.Concat(query, PtsNames.October, ", ");
            query = string.Concat(query, PtsNames.November, ", ");
            query = string.Concat(query, PtsNames.December, ", ");
            query = string.Concat(query, PtsNames.YearToDate, ", ");
            query = string.Concat(query, PtsNames.FullYear, ", ");
            query = string.Concat(query, "'", DateTime.Today.ToString("yyyy-MM-dd"), "' as [ReferenceDate], ");
            query = string.Concat(query, "'", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"), "' as [LastUpdate]");
            #endregion

            return string.Format(@"SELECT {0} FROM {1} Financials (NOLOCK)
                                    WHERE {2} = {3} AND ({4} = 'LAD' OR {5} = 'LAD')
                                        AND {6} BETWEEN {7} AND {8}",
                query, PtsNames.TableName, PtsNames.PeriodYear, year, PtsNames.SystemRegion, PtsNames.ClientRegion,
                EntrepotDatabaseNames.ApplicationBookOfWork.ProjectId, projectIdStart, projectIdEnd);
        }

        public static string GetQueryCreateTableNewYear(int year, bool history)
        {
            return string.Format(@"IF NOT EXISTS (SELECT 1 from [Pts{1}].[dbo].[sysobjects] where name='ApplicationBookOfWork{1}{0}' AND xtype='U')
                BEGIN
	                CREATE TABLE [Pts{1}].[dbo].[ApplicationBookOfWork{1}{0}] (
                        [AccountLevel1]                 VARCHAR (25)     NULL,
                        [AccountLevel2]                 VARCHAR (25)     NULL,
                        [AccountLevel3]                 VARCHAR (25)     NULL,
                        [InitiativeId]                  VARCHAR (30)     NULL,
                        [InitiativeName]                VARCHAR (255)    NULL,
                        [ProgramId]                     VARCHAR (30)     NULL,
                        [ProgramName]                   VARCHAR (255)    NULL,
                        [ProjectId]                     VARCHAR (20)     NULL,
                        [ProjectName]                   VARCHAR (200)    NULL,
                        [ProgramManager]                VARCHAR (255)    NULL,
                        [ProgramStartDate]              DATETIME         NULL,
                        [ProgramEndDate]                DATETIME         NULL,
                        [ApplicationId]                 INT              NULL,
                        [ApplicationName]               VARCHAR (150)    NULL,
                        [ApplicationStatus]             CHAR (1)         NULL,
                        [ProjectStatus]                 VARCHAR (100)    NULL,
                        [ProjectStartDate]              DATETIME         NULL,
                        [ProjectEndDate]                DATETIME         NULL,
                        [ProjectManager]                VARCHAR (150)    NULL,
                        [BillingClientGocCode]          VARCHAR (15)     NULL,
                        [Client3Name]                   VARCHAR (120)    NULL,
                        [Client4Name]                   VARCHAR (120)    NULL,
                        [Client5Name]                   VARCHAR (120)    NULL,
                        [Client6Name]                   VARCHAR (120)    NULL,
                        [Client7Name]                   VARCHAR (120)    NULL,
                        [Client8Name]                   VARCHAR (120)    NULL,
                        [Client9Name]                   VARCHAR (120)    NULL,
                        [Client10Name]                  VARCHAR (120)    NULL,
                        [ClientGocCode]                 VARCHAR (15)     NULL,
                        [ClientRegion]                  VARCHAR (64)     NULL,
                        [System5Name]                   VARCHAR (120)    NULL,
                        [System6Name]                   VARCHAR (120)    NULL,
                        [System7Name]                   VARCHAR (120)    NULL,
                        [System8Name]                   VARCHAR (120)    NULL,
                        [System9Name]                   VARCHAR (120)    NULL,
                        [System10Name]                  VARCHAR (120)    NULL,
                        [SystemGOCCode]                 VARCHAR (15)     NULL,
                        [SystemRegion]                  VARCHAR (64)     NULL,
                        [LOB]                           VARCHAR (250)    NULL,
                        [RuntheBankBuildtheBank]        VARCHAR (1)      NULL,
                        [ProgramProjectLinkIndicator]   VARCHAR (10)     NULL,
                        [WorkTypeFinancials]            VARCHAR (18)     NULL,
                        [InitiativeInvestimentCategory] VARCHAR (186)    NULL,
                        [ProgramInvestimentCategory]    VARCHAR (50)     NULL,
                        [ProgramCapitalizedIndicator]   VARCHAR (1)      NULL,
                        [ProgramStatus]                 VARCHAR (50)     NULL,
                        [ProjectCapitalization]         VARCHAR (15)     NULL,
                        [BowFlag]                       VARCHAR (10)     NULL,
                        [BowFlag2]                      VARCHAR (10)     NULL,
                        [ProjectType]                   VARCHAR (100)    NULL,
                        [ProgramCarryoverIndicator]     VARCHAR (1)      NULL,
                        [TaxonomyName]                  VARCHAR (100)    NULL,
                        [CURate]                        DECIMAL (9, 6)   NULL,
                        [FirstOfProgramDesc]            VARCHAR (2000)   NULL,
                        [AdjustedFlag]                  VARCHAR (10)     NULL,
                        [LobParent]                     VARCHAR (250)    NULL,
                        [January]                       DECIMAL (38, 18) NULL,
                        [February]                      DECIMAL (38, 18) NULL,
                        [March]                         DECIMAL (38, 18) NULL,
                        [April]                         DECIMAL (38, 18) NULL,
                        [May]                           DECIMAL (38, 18) NULL,
                        [June]                          DECIMAL (38, 18) NULL,
                        [July]                          DECIMAL (38, 18) NULL,
                        [August]                        DECIMAL (38, 18) NULL,
                        [September]                     DECIMAL (38, 18) NULL,
                        [October]                       DECIMAL (38, 18) NULL,
                        [November]                      DECIMAL (38, 18) NULL,
                        [December]                      DECIMAL (38, 18) NULL,
                        [YearToDate]                    DECIMAL (38, 18) NULL,
                        [FullYear]                      DECIMAL (38, 18) NULL,
                        [ReferenceDate]                 DATE             DEFAULT (getdate()) NOT NULL,
                        [LastUpdate]                    DATETIME         DEFAULT (getdate()) NOT NULL,
                        [ApplicationRetirementDate]     DATETIME         NULL
                    );
                END", year, history ? "History" : string.Empty);
        }

        #endregion
    }
}
